require 'fasterCSV'
require 'pathname'

 #This file contains all the methods that are begin used throughout the application for all ETL tasks.

 #This method gets the column specifications of a single column from the root table.
 #This is useful while transformations mainly column transforms.
 def get_the_col_spec_for_single_col(column_name,table_name,new_connection)
        col_struc = new_connection.execute("desc #{table_name}")
        col_struc.each do |col|
              column_one = first_letter_capital(col[0])
              column_two = first_letter_capital(column_name)
              if col[0] == column_name || col[0].upcase == column_name || col[0].downcase == column_name || column_one == column_two
                    return col[1]
              end	
        end	
  end

  # This method is for generating the column profile.  
  def generate_column_profile(column,table_name,connection)
      all_values = []
      all_values = []
      sum = 0
      square_sum = 0
      connection.select_all("select #{column[0]} from #{table_name}").each{|value|
          all_values << value[0].to_i
          sum = sum + value[0].to_i
        }
        all_values = all_values.sort
        puts "Column name is #{column[0]}"
        puts "Column datatype is #{column[1].split("(")[0]}"
        puts "Column size is #{column[1].split("(")[1].split(")")[0]}"
        puts "Maximum value of the column is #{all_values.last}"
        puts "Minimum value of the column is #{all_values.first}"
        mean_value = (sum*0.1)/(all_values.size)
        puts "Mean value of the column is #{mean_value}"
        # Calculating the standard deviation
            all_values.each{|value|
                  square_sum = square_sum + ((value - mean_value)*(value - mean_value))
            }
        puts "Standard deviation is #{Math.sqrt(square_sum/(all_values.size))}"
  end


  def generate_possible_third_table_name(first_table,second_table,connection)
      first_option = first_table.to_s+"_"+second_table.to_s
      all_tables = connection.tables
      for table in all_tables
          if table==first_option
              first_option = first_option+"new"
          end  
      end
      return first_option  
  end


  def add_column_if_does_not_exists(column_name,table_name,connection)
      all_columns = connection.execute("desc #{table_name}") 
      for column in all_columns
          if column[0].to_s == column_name.to_s
              return
          end  
      end
      connection.execute("ALTER TABLE #{table_name} ADD #{column_name} VARCHAR(255)")
  end
 
 
 
  # Given the relative position and the list of columns, this method determines what are the columns matching these positions.
  def get_columns_from_positions(value,all_columns)
        column_array = []
        column_names = []
        start_from = value.split("_")[0]
        number_of_columns = value.split("_")[1].to_i
        for column in all_columns
              column_array << column
        end
        if start_from == "last"
              column_array = column_array.reverse
        end	
        for loop_counter in 0..number_of_columns-1
              if column_array[loop_counter] != nil
                    column_names << column_array[loop_counter]
              end	
        end
        return column_names
  end			



  # Given the column numbers and the list of columns, this method determines what are the columns matching these positions.
  def get_columns_from_numbers(number_array,all_columns)
        column_counter = 1 
        column_names = []
        for column in all_columns 
              if number_array.include?column_counter
                  column_names << column	
              end	
              column_counter = column_counter + 1
        end
        return column_names
  end		



 #This method is handling some primary key related scenarios. These are as follows:
 #1.  In the first_table the primary key is already there and is diff from the one mentioned by user.
 #2.  In the  first_table the primary key is already there and is same as the one mentioned by user. 
 #3.  In the first_table there is no primary key.
 #4.  In the first_table the column mentioned by user is already there but it is not the primary key.

  def handle_primary_key(first_table_primary_key,first_table,new_connection)
          result_array = primary_key_column_exists(first_table_primary_key,first_table,new_connection)
          if result_array[0] == 1
                first_row_specs = get_the_col_spec_for_single_col(result_array[1],first_table,new_connection)
                puts "The primary key you have mentioned is already existing. So that has been taken."
          elsif	result_array[0] == 2
                puts "#{first_table} already has primary key other than the one you have mentioned."
                first_row_specs = 0
          elsif 	result_array[0] == 3
                new_connection.execute("ALTER TABLE #{first_table} ADD PRIMARY KEY (#{result_array[1]})")
                first_row_specs = get_the_col_spec_for_single_col(first_table_primary_key,first_table,new_connection)
          else 
                puts primary_key_column_exists(first_table_primary_key,first_table,new_connection)
                new_connection.execute("ALTER TABLE #{first_table} ADD #{first_table_primary_key} varchar(255)")
                new_connection.execute("ALTER TABLE #{first_table} ADD PRIMARY KEY (#{first_table_primary_key})")
                first_row_specs = "int(11)"
                puts "For #{first_table} non-existant column has been made as aprimary key."
                puts "Application has generated new column with same name"
          end		
          return first_row_specs
  end 


  
  # Given the table name and the primary key title, this method determines which column can  be a primary key based on simple heuristics. 
  def get_possible_primary_key(primary_key,table_name,connection)
        all_columns = connection.query("desc #{table_name}") 
        for column in all_columns
              if column[0] == primary_key || column[0] == first_letter_capital(primary_key) || column[0] == primary_key.capitalize || column[0] == primary_key.gsub(/\_/,"") 
                  return column[0]
              end	
        end
  end
 
 
 
  #This method verifies if the primary key mentioned in the config file is actually there in the table. 
  #This method ensures that application generates id as a default primary key in case of a missing primary key. 
  def primary_key_column_exists(primary_key,table,new_connection) 
          table_struc = new_connection.execute("desc #{table}") 
          for field in table_struc
                  if field.include?"PRIMARY KEY" or field.include?"primary key" or field.include?"PRI"
                          if field[0] == primary_key || field[0] == first_letter_capital(primary_key) || field[0] == primary_key.capitalize || field[0] == primary_key.gsub(/\_/,"") 
                                return [1,field[0]]
                          else	
                                return [2,"no_primary_key"]
                          end		
                  else
                          if field[0] == primary_key || field[0] == first_letter_capital(primary_key) || field[0] == primary_key.capitalize || field[0] == primary_key.gsub(/\_/,"") 
                              return [3,field[0]]
                          end	  
                  end	
          end
          return [4,"no_primary_key"]
  end	  


  # This method converts the primary key name into camel case for comparison.
  def first_letter_capital(field_name)
          field_names = field_name.split("_")
          for str in field_names
                str.capitalize!
          end	
          field_name = field_names.join("")
          return field_name
  end



  #If the table where data is to be loaded already exists then its headers are extracted so that insert data into correct columns. 
  def generate_header_file(tablename,connection)
        all_columns = connection.execute("desc #{tablename}") 
        header_file_handler = File.new("#{tablename}_header.csv","w")
        all_columns.each do |column_name|
              header_file_handler.write(column_name[0]+",")
        end		
        header_file_handler.close
        return
  end	  



  #Establishes a new connection based on the options given in the cofig file. 
  #This method also checks the configuration options and throws an error if none can be found.
  def create_new_connection
	connection_option = extract_connection_options
        if connection_option[0] == nil || connection_option[1] == nil || connection_option[2] == nil
                puts "The database connection could not be established. This might be due to one of the following options being null:
            1. Database name
            2. Database type
            3. Hostname"
        else
                database_type = connection_option[0]
                database = connection_option[1]
                host = connection_option[2]
                password = connection_option[3]
                username = connection_option[4]
                if connection_option[3].nil? || connection_option[3].empty?
                      new_connection = DBI.connect("DBI:#{connection_option[0].capitalize}:#{connection_option[1]}:#{connection_option[2]}", "#{connection_option[4]}", "")
                else
                      new_connection = DBI.connect("DBI:#{connection_option[0].capitalize}:#{connection_option[1]}:#{connection_option[2]}", "#{connection_option[4]}", "#{connection_option[3]}")
                end	
                return new_connection
        end	
  end


  #This method is extractin connection options from the global_connection.csv
  def extract_connection_options
        connection_options = [] 
        FasterCSV.foreach("global_connection.csv") do |row|
              for field_counter in 0..row.length
                    case row[field_counter]
                          when "database_type"
                                connection_options[0] = row[field_counter+1]
                          when "database"
                                connection_options[1] = row[field_counter+1]
                          when "host"
                                connection_options[2] = row[field_counter+1]
                          when "password"
                                connection_options[3] = row[field_counter+1]
                          when "username"
                                connection_options[4] = row[field_counter+1]
                    end		
              end	
        end
        return connection_options	
  end	 
 
 #This file create the global_connection.csv 
 #This file is refernced everytime an ETL task is run. 
 #Any changes in the file will be picked up from the next task run.
 def create_global_connection_file
      config_file = File.new("global_connection.csv","w")
      config_file.write("database_type,")
      config_file.write("\n")
      config_file.write("database,")
      config_file.write("\n")
      config_file.write("host,")
      config_file.write("\n")
      config_file.write("password,")
      config_file.write("\n")
      config_file.write("username,")
      config_file.close
 end

 #This method extracts names of all the root tables.
 #Important while doing the normalization transforms.
 def get_root_tables(filename) 
      root_table_array = []  
      FasterCSV.foreach("#{filename}") do |row|
          if row.first == "root table" 
              for field_counter in 1..row.length-1
                  root_table_array << row[field_counter]
              end
          end
      end	
      return root_table_array
 end

 #This file creates the structure file of the root table. 
 #Useful in all the transformations.
 def create_structure_csv_file(table_name,new_connection)
      config_file_handler = File.new("#{table_name}_structure.csv",'w') 
      table_structure = new_connection.execute("desc #{table_name}")
      table_structure.each do |table_info|
            config_file_handler.write(table_info[0])
            config_file_handler.write(",")
            config_file_handler.write(table_info[1])
            config_file_handler.write("\n")
      end
      config_file_handler.close	
 end

 #This method is similar to the first method in this file.
 #The only difference is it generates the CSV file for all the columns in a table.
 def get_the_col_specs(column_name,table_name)
      filename="#{table_name}_structure.csv"
      FasterCSV.foreach(filename) do |row|
            for field_counter in 0..row.length
                  if row[field_counter] == column_name
                      return row[field_counter+1]
                      break
                  end	
            end
      end
 end	 

 